<?php
namespace app\index\model;
use app\common\model\Base;
use think\Db;

class CaipaiList extends Base
{
    public function getLotteryDetailOfMall() {
        //select czid,kjjg from kjjg_list where id IN(SELECT max(qihao) FROM kjjg_list where kjjg>0 GROUP BY czid)
        //构建所有彩种最大QIHAO表(解决分组后排序的问题)
        $max_id_query = Db::name('kjjg_list')
                                  ->field('MAX(qihao),czid')
                                  ->where('kjjg','>','0')
                                  ->group('czid')
                                  ->buildSql();
        //构建开奖结果表(解决开奖结果与彩种表的关联问题)
        $max_czid_query = Db::name('kjjg_list')
                                  ->field('czid,kjjg,qihao')
                                  ->where("(qihao,czid) IN({$max_id_query})")
                                  ->buildSql();
        //SELECT COUNT(*) FROM time GROUP BY czid
        //构建彩种每天期数表(解决期数与彩种表关联的问题)
        $periods_query = Db::name('time')
                                  ->field('czid,COUNT(*) count')
                                  ->group('czid')
                                  ->buildSql();
        //查询所有彩种和每种彩最新开奖及每天开奖期数
        $openclsit = Db::name('caipai_list')->alias('cl')
                                  ->join("{$max_czid_query} klq",'cl.id=klq.czid')
                                  ->join("{$periods_query} tq",'cl.id=tq.czid')
                                  ->field('cl.*,klq.kjjg,tq.count,klq.qihao')
                                  ->where('cp_if_open',1)
                                  ->order('cl.cp_dn_px','DESC')
                                  ->select();
        
        return $openclsit;
    }
}